﻿Imports MySql.Data.MySqlClient

Public Class CGuruMengajar
    Inherits CContext

    Private INSERT_COMMAND As String
    Private UPDATE_COMMAND As String
    Private DELETE_COMMAND As String
    Private SELECT_COMMAND As String

    Public Sub New()
        MyBase.New()
    End Sub

    ''' <summary>
    ''' Mengembalikan daftar kelas yang di ajar oleh guru tertentu pada semester tertentu
    ''' </summary>
    ''' <param name="idGuru"></param>
    ''' <param name="idSemester"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function getKelas(idGuru As Integer, idSemester As Integer) As DataTable
        Me.SELECT_COMMAND = " select distinct(m.id_kls) as id,k.nama from tbl_mengajar m inner join tbl_kelas k on m.id_kls = k.id where m.id_smstr = " & idSemester & " and m.id_guru = " & idGuru
        Return MyBase.SelectData(SELECT_COMMAND)
    End Function

    Public Function getKelasLeftJoin(idguru As Integer, idsemester As Integer) As DataTable
        Me.SELECT_COMMAND = "select distinct(k.id) as id_kls, k.nama as nm_kls , m.id_guru  from tbl_kelas k left join (select * from tbl_mengajar where id_guru  = " & idguru & " and id_smstr = " & idsemester & ") as m on k.id = m.id_kls"
        Return MyBase.SelectData(SELECT_COMMAND)
    End Function


    ''' <summary>
    ''' get daftar mata pelajaran yg diajar guru dan yg tidak diajar (id_guru,id_mp,nama,id_smstr)
    ''' </summary>
    ''' <param name="idguru"></param>
    ''' <param name="idKelas"></param>
    ''' <param name="idSemester"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function getMataPelajaranLeftJoin(idguru As Integer, idKelas As Integer, idSemester As Integer) As DataTable
        Me.SELECT_COMMAND = "select k.id_guru,k.id_mp,mp.nama,mg.id_smstr from tbl_konsentrasi k left join (select m.id_guru, m.id_mp, m.id_kls, m.id_smstr from tbl_mengajar m where m.id_guru = " & idguru & " and m.id_kls = " & idKelas & " and m.id_smstr = " & idSemester & ") mg on (k.id_guru = mg.id_guru and k.id_mp = mg.id_mp) inner join tbl_mata_pelajaran mp on k.id_mp = mp.id where k.id_guru = " & idguru
        Return MyBase.SelectData(SELECT_COMMAND)
    End Function


    ''' <summary>
    ''' mengembalikan daftar mata pelajaran yang di ajar oleh guru di semester dan kelas tertentu
    ''' (id,nama)
    ''' </summary>
    ''' <param name="idGuru"></param>
    ''' <param name="idSemester"></param>
    ''' <param name="idKelas"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function getMP(idGuru As Integer, idSemester As Integer, idKelas As Integer) As DataTable
        Me.SELECT_COMMAND = "select m.id_mp as id, p.nama from tbl_mengajar m inner join tbl_mata_pelajaran p on m.id_mp = p.id where m.id_smstr = " & idSemester & " and m.id_guru = " & idGuru & " and m.id_kls = " & idKelas
        Return MyBase.SelectData(SELECT_COMMAND)
    End Function

    ''' <summary>
    ''' Data table kelas yang di ajar oleh guru tertentu 
    ''' id, nama, id_guru,id_smstr
    ''' </summary>
    ''' <param name="idGuru"></param>
    ''' <param name="idSemester"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function getKelasByIdGuruIdSemester(ByVal idGuru As Integer, ByVal idSemester As Integer) As DataTable
        Me.SELECT_COMMAND = "select distinct(k.id), k.nama, m.id_guru,m.id_smstr from tbl_kelas k left join (select * from tbl_mengajar tm where tm.id_guru = " & idGuru & " and tm.id_smstr = " & idSemester & " )  m on k.id = m.id_kls"
        Return MyBase.SelectData(SELECT_COMMAND)
    End Function

    ''' <summary>
    ''' Daftar mata pelajaran yang di ajar oleh guru pada kelas dan semester tertentu
    ''' id_mp, nama
    ''' </summary>
    ''' <param name="idGuru"></param>
    ''' <param name="idKelas"></param>
    ''' <param name="idSemester"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function getMataPelajaranByidGuruIdKelasIdSemester(ByVal idGuru As Integer, ByVal idKelas As Integer, ByVal idSemester As Integer) As DataTable
        Me.SELECT_COMMAND = "select mg.id_mp, mp.nama from tbl_mengajar mg inner join tbl_mata_pelajaran mp on mg.id_mp = mp.id where mg.id_guru = " & idGuru & " and mg.id_kls = " & idKelas & " and mg.id_smstr = " & idSemester
        Return MyBase.SelectData(SELECT_COMMAND)
    End Function

    Public Sub rubahTSql()

    End Sub

    ''' <summary>
    ''' daftar kelas by id guru dan id semester
    ''' return id_guru, nama_guru, id_kls, nama_kls, id_smstr
    ''' </summary>
    ''' <param name="idGuru"></param>
    ''' <param name="idSemester"></param>
    ''' <returns>Data Table</returns>
    ''' <remarks></remarks>
    Public Function getKelasOnlyByIdGuruIdSemester(ByVal idGuru As Integer, ByVal idSemester As Integer) As DataTable
        Me.SELECT_COMMAND = "select distinct(m.id_guru), g.nama as nama_guru, m.id_kls, k.nama as nama_kls, m.id_smstr from tbl_mengajar m inner join tbl_guru g on m.id_guru = g.id inner join tbl_kelas k on m.id_kls = k.id where m.id_guru = " & idGuru & " and m.id_smstr = " & idSemester
        Return MyBase.SelectData(SELECT_COMMAND)
    End Function


    ''' <summary>
    ''' Daftar mata pelajaran yang di ajar oleh guru per semester per kelas
    ''' </summary>
    ''' <param name="idGuru"></param>
    ''' <param name="idSemester"></param>
    ''' <param name="idKelas"></param>
    ''' <returns>id_guru,id_mp,nanma, id_kls, id_smstr </returns>
    ''' <remarks></remarks>
    Public Function getMataPelajaran(ByVal idGuru As Integer, ByVal idSemester As Integer, ByVal idKelas As Integer) As DataTable
        Me.SELECT_COMMAND = "select k.id_guru, k.id_mp , mp.nama, m.id_kls,m.id_smstr from tbl_konsentrasi k inner join tbl_mata_pelajaran mp on k.id_mp = mp.id  left join tbl_mengajar m on k.id_guru = m.id_guru and k.id_mp=m.id_mp and m.id_kls = " & idKelas & " and m.id_smstr=" & idSemester & " where k.id_guru = " & idGuru

        Return MyBase.SelectData(SELECT_COMMAND)
    End Function

    Public Sub rubahTSql(ByVal idGuru As Integer, ByVal idKelas As Integer, ByVal idSemester As Integer, ByVal dataMengajar As DataTable)
        Dim _TMysql As MySql.Data.MySqlClient.MySqlTransaction
        MyBase.MySqlConnect.Open()
        _TMysql = MyBase.MySqlConnect.BeginTransaction

        Try

            'hapus data lama
            DELETE_COMMAND = "DELETE FROM TBL_MENGAJAR WHERE ID_GURU=" & idGuru & " AND ID_KLS = " & idKelas & " AND ID_SMSTR = " & idSemester
            Dim MYSQL_DELETE_CMD As MySql.Data.MySqlClient.MySqlCommand = MyBase.MySqlConnect.CreateCommand
            With MYSQL_DELETE_CMD
                .CommandText = DELETE_COMMAND
                .Transaction = _TMysql
                .ExecuteNonQuery()
            End With

            Dim MYSQL_INSERT_CMD As MySql.Data.MySqlClient.MySqlCommand
            For Each aRow As DataRow In dataMengajar.Rows
                If Not IsDBNull(aRow.Item("id_kls")) Or aRow.Item("id_kls").ToString.Trim <> "" Then
                    MYSQL_INSERT_CMD = MyBase.MySqlConnect.CreateCommand
                    With MYSQL_INSERT_CMD
                        .CommandText = "insert into tbl_mengajar (id_guru,id_mp,id_kls,id_smstr) values (" & aRow.Item("id_guru") & "," & aRow.Item("id_mp") & "," & aRow.Item("id_kls") & "," & aRow.Item("id_smstr") & ")"
                        .Transaction = _TMysql
                        .ExecuteNonQuery()
                    End With
                End If
            Next

            'commit command
            _TMysql.Commit()
        Catch ex As Exception
            _TMysql.Rollback()
            MsgBox(ex.Message)
        Finally
            MyBase.MySqlConnect.Close()
        End Try
    End Sub

    ''' <summary>
    ''' simpan data mengajar
    ''' </summary>
    ''' <param name="dataMengajar">id_guru,id_mp,id_kls,id_smstr</param>
    ''' <remarks></remarks>
    Public Sub simpan(ByVal idGuru As Integer, ByVal idKelas As Integer, ByVal idSemester As Integer, ByVal dataMengajar As DataTable)
        Dim _TMysql As MySql.Data.MySqlClient.MySqlTransaction
        MyBase.MySqlConnect.Open()
        _TMysql = MyBase.MySqlConnect.BeginTransaction

        Try

            'hapus data mengajar lama
            DELETE_COMMAND = "DELETE FROM TBL_MENGAJAR WHERE ID_GURU=" & idGuru & " AND ID_KLS = " & idKelas & " AND ID_SMSTR = " & idSemester
            Dim MYSQL_DELETE_CMD As MySql.Data.MySqlClient.MySqlCommand = MyBase.MySqlConnect.CreateCommand
            With MYSQL_DELETE_CMD
                .CommandText = DELETE_COMMAND
                .Transaction = _TMysql
                .ExecuteNonQuery()
            End With

            For Each aRow As DataRow In dataMengajar.Rows
                Dim INSERT_MENGAJAR_COMMAND As MySql.Data.MySqlClient.MySqlCommand = MyBase.MySqlConnect.CreateCommand

                With INSERT_MENGAJAR_COMMAND
                    .CommandText = "insert into tbl_mengajar (id_guru,id_mp,id_kls,id_smstr) values (?id_guru,?id_mp,?id_kls,?id_smstr)"
                    .Parameters.Add(New MySqlParameter("id_smstr", aRow.Item("id_smstr")))
                    .Parameters.Add(New MySqlParameter("id_kls", aRow.Item("id_kls")))
                    .Parameters.Add(New MySqlParameter("id_guru", aRow.Item("id_guru")))
                    .Parameters.Add(New MySqlParameter("id_mp", aRow.Item("id_mp")))

                    'MsgBox(aRow.Item("id_smstr") & vbTab & aRow.Item("id_kls") & vbTab & aRow.Item("id_guru") & vbTab & aRow.Item("id_mp"))
                    .Transaction = _TMysql
                    .ExecuteNonQuery()
                End With
            Next

            'commit command
            _TMysql.Commit()
        Catch ex As Exception
            _TMysql.Rollback()
            Throw ex
        Finally
            MyBase.MySqlConnect.Close()
        End Try
    End Sub

End Class
